#!/bin/bash
# File Name: check_pk.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-02-22 16:44:34
# Function: 检查业务表的主键情况
#########################################################################
:<<comment
变量注释:
db_name_arry  : 业务库的库名，一般为'mafengwo'，要根据实际情况配置，多个库用空格分开;
mysql_user    : 连接mysql用户名;
mysql_passwd  : 连接mysql的密码;
log_file      : 日志文件;
mysql_host    : 节点的ip地址
mysql_port    : mysql端口号
comment
db_name_arry=(db1 db2)
mysql_user="xxxx"
mysql_passwd="xxxx"
log_file="pri_result.log"
mysql_host="172.18.xx.xxx"
mysql_port="33xx"
  
#以下无需编辑修改
function f_check()
{
	db_name="${1}"
	echo -e "\033[33mCheck primary for \"${db_name}\" database\033[0m"
	echo -e "\033[32m--------------------------------------------------------------------------------\033[0m"
	mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${mysql_host} -P${mysql_port} ${db_name}"
	${mysql_comm} -NBe "select 1;" >/dev/null 2>&1
	if [ $? -ne 0 ]
	then
		echo
		echo -e "\033[31m[$(date "+%F %H:%M:%S")] [ERROR] Can't connect to MySQL server on '${mysql_user}@${mysql_host} for mysql_port:${mysql_port}'\033[0m"
		echo
		exit 1
	else
		>${log_file}
	fi
	table=($(${mysql_comm} -NBe "show tables" 2>/dev/null))
	mkdir -p ${db_name}
	for ((i=0;i<${#table[@]};i++))
	do
		#pri=$(${mysql_comm} -NBe "show create table \`${table[${i}]}\`\\G" 2> /dev/null|grep -c "PRIMARY KEY")
		pri=$(${mysql_comm} -e "desc \`${table[${i}]}\`" 2> /dev/null|grep -c "PRI")
		if [ "${pri}x" == "0x" ]
		then
			echo -e "\033[31m[$(date "+%F %H:%M:%S")] [ERROR] ${table[${i}]}:No primary key."
			${mysql_comm} -NBe "show create table \`${table[${i}]}\`\\G" 2> /dev/null > ${db_name}/${table[${i}]}.log
		else
			echo -e "\033[32m[$(date "+%F %H:%M:%S")] [OK] ${table[${i}]}:There is primary key."
		fi
	done
	[ -d "${db_name}" ] && rmdir ${db_name} >/dev/null 2<&1
	echo -e "\033[32m--------------------------------------------------------------------------------\033[0m"
	if [ -d "${db_name}" ]
	then
		echo -e "\033[31m[$(date "+%F %H:%M:%S")] [ERROR] 存在部分业务表无主键，详情请查看${db_name}目录中的【表名.log】文件。\033[0m"
	else
		echo -e "\033[32m[$(date "+%F %H:%M:%S")] [OK] 所有业务表均有主键。\033[0m"
	fi
}
 
for ((db=0;db<${#db_name_arry[@]};db++))
do
	echo -e "\033[32m================================================================================\033[0m"
	f_check ${db_name_arry[${db}]}
	echo -e "\033[32m================================================================================\033[0m"
	echo
	echo
	echo
done
